package com.jiudao.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.google.gson.JsonArray;
import com.google.gson.JsonObject;
import com.jiudao.entity.AllAmount;
import com.jiudao.entity.Applist;
import com.jiudao.entity.Branch;
import com.jiudao.entity.CustomerGroup;
import com.jiudao.entity.Empno;
import com.jiudao.entity.JylRiskconPerson;
import com.jiudao.entity.Moneysch;
import com.jiudao.entity.PersonNewOld;
import com.jiudao.entity.RiskMark;
import com.jiudao.entity.RiskNum;
import com.jiudao.entity.RiskType;
import com.jiudao.entity.Riskcon;
import com.jiudao.entity.Risklist;
import com.jiudao.jdbc.Client;

@Repository
public class BaseDao {
	protected static Client client=new Client();
	protected final Logger logger=Logger.getLogger(this.getClass());
	
	@Autowired
	protected Riskcon riskcon;
	@Autowired
	protected Risklist risklist;
	@Autowired
	protected Branch branch;
	@Autowired
	protected RiskMark riskmark;
	@Autowired
	protected RiskNum risknum;
	@Autowired
	protected Applist applist;
	@Autowired
	protected CustomerGroup customerGroup;
	@Autowired
	protected JylRiskconPerson jylRiskconPerson;
	@Autowired
	protected AllAmount allAmount;
	@Autowired
	protected RiskType riskType;
	@Autowired
	protected Empno empno;
	@Autowired
	protected PersonNewOld personNewOld;
	@Autowired
	protected Moneysch moneysch;
	
	//执行查询方法
	protected JsonArray executeQuery(String sql, String columns) {
		
		Connection conn = null;
		Statement state = null;
		ResultSet rs=null;
		try {
			conn = client.getConnection();
			state = conn.createStatement();
			rs = state.executeQuery(sql);
			JsonArray arr = new JsonArray();
			JsonObject json;
			while (rs.next()) {
				json = new JsonObject();
				String[] columnList = columns.split(",");
				for (String str : columnList) {
					json.addProperty(str, rs.getString(str));
				}
				arr.add(json);
			}
			return arr;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally{
			try {
				rs.close();
				state.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

//	public static void main(String[] args) {
//		String sqltest = "select classcode as classcode, SUM(tmount) as tmount FROM riskcon GROUP BY classcode";
//		String col = "classcode,tmount";
//		JsonArray a = executeQuery(sqltest, col);
//	}
	
	/*public static void main(String[] args) {
		Riskcon riskcon = new Riskcon();
		AllAmount allAmount = new AllAmount();
		CustomerGroup customerGroup = new CustomerGroup();
		Risklist risklist = new Risklist();
		RiskType riskType = new RiskType();
		String sql= " SELECT sum( "+allAmount.column(AllAmount.ALL_AMOUNT)+" ) SUM_AMOUNT,count(distinct "+riskcon.column(Riskcon.POLICYNO)+" ) COUNT_POLICYNO,"+riskType.column(RiskType.CLASSTYPE) +" from "+riskcon.tablename()
		  +" inner JOIN "+allAmount.tablename()
		  +" on "+riskcon.column(Riskcon.POLICYNO) +" = "+allAmount.column(Applist.POLICYNO) 
		  +" and "+riskcon.column(Riskcon.CLASSCODE) +" = "+allAmount.column(Applist.CLASSCODE)
		  +" left join "+customerGroup.tablename()
		  +" on "+riskcon.column(Riskcon.POLICYNO) +" = "+customerGroup.column(CustomerGroup.POLICYNO) 
		  +" left join "+risklist.tablename()
		  +" on "+riskcon.column(Riskcon.CLASSCODE) +" = "+risklist.column(Risklist.CLASSCODE)
		  +" left join "+riskType.tablename()
		  +" on "+risklist.column(Risklist.CLASSNAME) +" = "+riskType.column(RiskType.CLASSNAME)
		  +" group by "+riskType.column(RiskType.CLASSTYPE);

         String columns="SUM_AMOUNT,COUNT_POLICYNO,"+RiskType.CLASSTYPE;
         JsonArray jsonArray = executeQuery(sql, columns);
         JsonArray array = new JsonArray();
 		JsonObject objectA = new JsonObject();
 		objectA.addProperty("CLASSTYPE", "a");
 		double moneyA = 0;
 		double countA = 0;
 		JsonObject objectB = new JsonObject();
 		objectB.addProperty("CLASSTYPE", "b");
 		double moneyB = 0;
 		double countB = 0;
 		JsonObject objectC = new JsonObject();
 		objectC.addProperty("CLASSTYPE", "c");
 		double moneyC = 0;
 		double countC = 0;
 		JsonObject objectD = new JsonObject();
 		objectD.addProperty("CLASSTYPE", "d");
 		double moneyD = 0;
 		double countD = 0;
 		for (JsonElement jsonElement : jsonArray) {
 			JsonObject object = jsonElement.getAsJsonObject();
 			System.out.println(object.get("CLASSTYPE"));
 			if ((!object.get("CLASSTYPE").toString().equals("null"))&&object.get("CLASSTYPE").toString().indexOf("a")!=-1) {
 				moneyA += object.get("SUM_AMOUNT").getAsDouble();
 				countA += object.get("COUNT_POLICYNO").getAsDouble();
 			}
 			if ((!object.get("CLASSTYPE").toString().equals("null"))&&object.get("CLASSTYPE").toString().indexOf("b")!=-1) {
 				moneyB += object.get("SUM_AMOUNT").getAsDouble();
 				countB += object.get("COUNT_POLICYNO").getAsDouble();
 			}
 			if ((!object.get("CLASSTYPE").toString().equals("null"))&&object.get("CLASSTYPE").toString().indexOf("c")!=-1) {
 				moneyC += object.get("SUM_AMOUNT").getAsDouble();
 				countC += object.get("COUNT_POLICYNO").getAsDouble();
 			}
 			if ((!object.get("CLASSTYPE").toString().equals("null"))&&object.get("CLASSTYPE").toString().indexOf("d")!=-1) {
 				moneyD += object.get("SUM_AMOUNT").getAsDouble();
 				countD += object.get("COUNT_POLICYNO").getAsDouble();
 			}
 		
 		objectA.addProperty("AVERAGE",moneyA/countA);
 		objectB.addProperty("AVERAGE",moneyB/countB);
 		objectC.addProperty("AVERAGE",moneyC/countC);
 		objectD.addProperty("AVERAGE",moneyD/countD);
 		array.add(objectA);
 		array.add(objectB);
 		array.add(objectC);
 		array.add(objectD);
        	 System.out.println(array);
	
 		}
	}*/
}
